/**  定义Index相关的接口 */
const express = require("express");
const router = express.Router();
const Response = require("../utils/Response.js");
// 引入mysql连接池
const pool = require("../utils/db.js");

// 查询用户男女数量
router.get("/index/sexcount", (req, resp) => {
  let sql = "select user_sex, count(user_sex) as count from user_b group by user_sex";
  pool.query(sql, [], (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    resp.send(Response.ok({ result }));
  });
});

// 查询用户城市分布
router.get("/index/citycount", (req, resp) => {
  let sql = "select city_name, count(city_name) as count from user_b group by city_name";
  pool.query(sql, [], (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    resp.send(Response.ok({ result }));
  });
});

// 查询驾校评分分布
router.get("/index/grade", (req, resp) => {
  let sql = `SELECT
    SUM( CASE WHEN school_score >= 4 THEN 1 ELSE 0 END ) AS VeryNice ,
    SUM( CASE WHEN school_score < 4 AND school_score >= 3 THEN 1 ELSE 0 END ) AS Nice ,
    SUM( CASE WHEN school_score < 3 AND school_score >= 2 THEN 1 ELSE 0 END ) AS General ,
    SUM( CASE WHEN school_score < 2 AND school_score >= 1 THEN 1 ELSE 0 END ) AS Poor ,
    SUM( CASE WHEN school_score < 1  THEN 1 ELSE 0 END ) AS VeryPoor 
  FROM
	school`;
  pool.query(sql, [], (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    resp.send(Response.ok({ result }));
  });
});

// 将router对象导出
module.exports = router;
